
drop table jms_dm.dm_customer_province_bill_amount_temp_dt;

create table jms_dm.dm_customer_province_bill_amount_temp_dt (
  `pick_network_code` varchar(200) NULL COMMENT "网点编码",
  `first_franchisee_code` varchar(200) NULL COMMENT "所属加盟商编码",
  `send_financial_center_code` varchar(200) NULL COMMENT "所属财务中心code",
  `date_time` date NULL COMMENT "日期",
  `customer_code` varchar(200) NULL COMMENT "客户Code",
  `receiver_province_id` varchar(200) NULL COMMENT "目的省份ID",
  `pick_network_name` varchar(500) NULL COMMENT "网点名称",
  `first_franchisee_name` varchar(500) NULL COMMENT "所属加盟商name",
  `send_financial_center_name` varchar(500) NULL COMMENT "所属财务中心名称",
  `customer_name` varchar(500) NULL COMMENT "客户名称",
  `receiver_province_name` varchar(500) NULL COMMENT "目的省份名称",
  `total_bill_count` decimal(16, 0) NULL COMMENT "总票数",
  `total_weight_count` decimal(16, 2) NULL COMMENT "总重量（内部计费总量）",
  `total_average_weight` decimal(16, 2) NULL COMMENT "均重（总重量/总票数）",
  `total_freight` decimal(16, 2) NULL COMMENT "收入（总运费）",
  `total_transfer_fee` decimal(16, 2) NULL COMMENT "中转费",
  `total_operation_fee` decimal(16, 2) NULL COMMENT "操作费",
  `total_base_dispatch_fee` decimal(16, 2) NULL COMMENT "基础派费",
  `total_piece_fee` decimal(16, 2) NULL COMMENT "续重派费",
  `total_sales_price` decimal(16, 2) NULL COMMENT "面单费",
  `total_bill_amount` decimal(16, 2) NULL COMMENT "取件提成",
  `total_prime_cost` decimal(16, 2) NULL COMMENT "成本",
  `total_profit` decimal(16, 2) NULL COMMENT "利润",
  `total_average_profit` decimal(16, 2) NULL COMMENT "均利（利润/票数）",
  `total_average_prime` decimal(16, 2) NULL COMMENT "单票成本(成本/总票数)",
  `zero_bill_count` decimal(16, 0) NULL COMMENT "w=0票数",
  `zero_weight` decimal(16, 2) NULL COMMENT "w=0重量",
  `zero_average_weight` decimal(16, 2) NULL COMMENT "w=0均重",
  `zero_freight` decimal(16, 2) NULL COMMENT "w=0收入",
  `zero_prime_cost` decimal(16, 2) NULL COMMENT "w=0成本",
  `zero_profit` decimal(16, 2) NULL COMMENT "w=0利润",
  `zero_average_profit` decimal(16, 2) NULL COMMENT "w=0均利（利润/票数）",
  `one_bill_count` decimal(16, 2) NULL COMMENT "w>0 w<=0.5票数",
  `one_weight` decimal(16, 2) NULL COMMENT "w>0 w<=0.5重量",
  `one_average_weight` decimal(16, 2) NULL COMMENT "w>0 w<=0.5均重",
  `one_freight` decimal(16, 2) NULL COMMENT "w>0 w<=0.5收入",
  `one_prime_cost` decimal(16, 2) NULL COMMENT "w>0 w<=0.5成本",
  `one_profit` decimal(16, 2) NULL COMMENT "w>0 w<=0.5利润",
  `one_average_profit` decimal(16, 2) NULL COMMENT "w>0 w<=0.5均利（利润/票数）",
  `two_bill_count` decimal(16, 0) NULL COMMENT "w>0.5 w<=3.2票数",
  `two_weight` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2重量",
  `two_average_weight` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2均重",
  `two_freight` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2收入",
  `two_prime_cost` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2成本",
  `two_profit` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2利润",
  `two_average_profit` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2均利（利润/票数）",
  `three_bill_count` decimal(16, 0) NULL COMMENT "w>3.2 w<=5.2票数",
  `three_weight` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2重量",
  `three_average_weight` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2均重",
  `three_freight` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2收入",
  `three_prime_cost` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2成本",
  `three_profit` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2利润",
  `three_average_profit` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2均利（利润/票数）",
  `four_bill_count` decimal(16, 0) NULL COMMENT "w>5.2 w<=10.3票数",
  `four_weight` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3重量",
  `four_average_weight` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3均重",
  `four_freight` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3收入",
  `four_prime_cost` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3成本",
  `four_profit` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3利润",
  `four_average_profit` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3均利（利润/票数）",
  `five_bill_count` decimal(16, 0) NULL COMMENT "w>10.3票数",
  `five_weight` decimal(16, 2) NULL COMMENT "w>10.3重量",
  `five_average_weight` decimal(16, 2) NULL COMMENT "w>10.3均重",
  `five_freight` decimal(16, 2) NULL COMMENT "w>10.3收入",
  `five_prime_cost` decimal(16, 2) NULL COMMENT "w>10.3成本",
  `five_profit` decimal(16, 2) NULL COMMENT "w>10.3利润",
  `five_average_profit` decimal(16, 2) NULL COMMENT "w>10.3均利（利润/票数）",
  `six_bill_count` decimal(16, 0) NULL COMMENT "0.5＜W≤1票数",
  `six_weight` decimal(16, 2) NULL COMMENT "0.5＜W≤1重量",
  `six_average_weight` decimal(16, 2) NULL COMMENT "0.5＜W≤1均重",
  `six_freight` decimal(16, 2) NULL COMMENT "0.5＜W≤1收入",
  `six_prime_cost` decimal(16, 2) NULL COMMENT "0.5＜W≤1成本",
  `six_profit` decimal(16, 2) NULL COMMENT "0.5＜W≤1利润",
  `six_average_profit` decimal(16, 2) NULL COMMENT "0.5＜W≤1均利",
  `seven_bill_count` decimal(16, 0) NULL COMMENT "1＜W≤2票数",
  `seven_weight` decimal(16, 2) NULL COMMENT "1＜W≤2重量",
  `seven_average_weight` decimal(16, 2) NULL COMMENT "1＜W≤2均重",
  `seven_freight` decimal(16, 2) NULL COMMENT "1＜W≤2收入",
  `seven_prime_cost` decimal(16, 2) NULL COMMENT "1＜W≤2成本",
  `seven_profit` decimal(16, 2) NULL COMMENT "1＜W≤2利润",
  `seven_average_profit` decimal(16, 2) NULL COMMENT "1＜W≤2均利",
  `eight_bill_count` decimal(16, 0) NULL COMMENT "2＜W≤3.2票数",
  `eight_weight` decimal(16, 2) NULL COMMENT "2＜W≤3.2重量",
  `eight_average_weight` decimal(16, 2) NULL COMMENT "2＜W≤3.2均重",
  `eight_freight` decimal(16, 2) NULL COMMENT "2＜W≤3.2收入",
  `eight_prime_cost` decimal(16, 2) NULL COMMENT "2＜W≤3.2成本",
  `eight_profit` decimal(16, 2) NULL COMMENT "2＜W≤3.2利润",
  `eight_average_profit` decimal(16, 2) NULL COMMENT "2＜W≤3.2均利",
  `nine_bill_count` decimal(16, 0) NULL COMMENT "5.2＜W≤20票数",
  `nine_weight` decimal(16, 2) NULL COMMENT "5.2＜W≤20重量",
  `nine_average_weight` decimal(16, 2) NULL COMMENT "5.2＜W≤20均重",
  `nine_freight` decimal(16, 2) NULL COMMENT "5.2＜W≤20收入",
  `nine_prime_cost` decimal(16, 2) NULL COMMENT "5.2＜W≤20成本",
  `nine_profit` decimal(16, 2) NULL COMMENT "5.2＜W≤20利润",
  `nine_average_profit` decimal(16, 2) NULL COMMENT "5.2＜W≤20均利",
  `ten_bill_count` decimal(16, 0) NULL COMMENT "W>20票数",
  `ten_weight` decimal(16, 2) NULL COMMENT "W>20重量",
  `ten_average_weight` decimal(16, 2) NULL COMMENT "W>20均重",
  `ten_freight` decimal(16, 2) NULL COMMENT "W>20收入",
  `ten_prime_cost` decimal(16, 2) NULL COMMENT "W>20成本",
  `ten_profit` decimal(16, 2) NULL COMMENT "W>20利润",
  `ten_average_profit` decimal(16, 2) NULL COMMENT "W>20均利",
  `if_in_province_bill` int(11) NULL COMMENT "是否省内件",
  `total_with_freight` bigint(20) NULL COMMENT "有运费票数",
  `total_without_freight` bigint(20) NULL COMMENT "无运费票数",
  `cust_level` varchar(20) NULL COMMENT "客户等级"
) ENGINE=OLAP
DUPLICATE KEY(`pick_network_code`, `first_franchisee_code`, `send_financial_center_code`, `date_time`, `customer_code`, `receiver_province_id`)
COMMENT "客户目的省份--收入汇总表"
PARTITION BY RANGE(`date_time`)
(PARTITION p20230501 VALUES [('2023-05-01'), ('2023-05-02')),
PARTITION p20230502 VALUES [('2023-05-02'), ('2023-05-03')),
PARTITION p20230503 VALUES [('2023-05-03'), ('2023-05-04')),
PARTITION p20230504 VALUES [('2023-05-04'), ('2023-05-05')),
PARTITION p20230505 VALUES [('2023-05-05'), ('2023-05-06')),
PARTITION p20230506 VALUES [('2023-05-06'), ('2023-05-07')),
PARTITION p20230507 VALUES [('2023-05-07'), ('2023-05-08')),
PARTITION p20230508 VALUES [('2023-05-08'), ('2023-05-09')),
PARTITION p20230509 VALUES [('2023-05-09'), ('2023-05-10')),
PARTITION p20230510 VALUES [('2023-05-10'), ('2023-05-11')),
PARTITION p20230511 VALUES [('2023-05-11'), ('2023-05-12')),
PARTITION p20230512 VALUES [('2023-05-12'), ('2023-05-13')),
PARTITION p20230513 VALUES [('2023-05-13'), ('2023-05-14')),
PARTITION p20230514 VALUES [('2023-05-14'), ('2023-05-15')),
PARTITION p20230515 VALUES [('2023-05-15'), ('2023-05-16')),
PARTITION p20230516 VALUES [('2023-05-16'), ('2023-05-17')),
PARTITION p20230517 VALUES [('2023-05-17'), ('2023-05-18')),
PARTITION p20230518 VALUES [('2023-05-18'), ('2023-05-19')),
PARTITION p20230519 VALUES [('2023-05-19'), ('2023-05-20')),
PARTITION p20230520 VALUES [('2023-05-20'), ('2023-05-21')),
PARTITION p20230521 VALUES [('2023-05-21'), ('2023-05-22')),
PARTITION p20230522 VALUES [('2023-05-22'), ('2023-05-23')),
PARTITION p20230523 VALUES [('2023-05-23'), ('2023-05-24')),
PARTITION p20230524 VALUES [('2023-05-24'), ('2023-05-25')),
PARTITION p20230525 VALUES [('2023-05-25'), ('2023-05-26')),
PARTITION p20230526 VALUES [('2023-05-26'), ('2023-05-27')),
PARTITION p20230527 VALUES [('2023-05-27'), ('2023-05-28')),
PARTITION p20230528 VALUES [('2023-05-28'), ('2023-05-29')),
PARTITION p20230529 VALUES [('2023-05-29'), ('2023-05-30')),
PARTITION p20230530 VALUES [('2023-05-30'), ('2023-05-31')),
PARTITION p20230531 VALUES [('2023-05-31'), ('2023-06-01')),
PARTITION p20230601 VALUES [('2023-06-01'), ('2023-06-02')),
PARTITION p20230602 VALUES [('2023-06-02'), ('2023-06-03')),
PARTITION p20230603 VALUES [('2023-06-03'), ('2023-06-04')),
PARTITION p20230604 VALUES [('2023-06-04'), ('2023-06-05')),
PARTITION p20230605 VALUES [('2023-06-05'), ('2023-06-06')),
PARTITION p20230606 VALUES [('2023-06-06'), ('2023-06-07')),
PARTITION p20230607 VALUES [('2023-06-07'), ('2023-06-08')),
PARTITION p20230608 VALUES [('2023-06-08'), ('2023-06-09')),
PARTITION p20230609 VALUES [('2023-06-09'), ('2023-06-10')),
PARTITION p20230610 VALUES [('2023-06-10'), ('2023-06-11')),
PARTITION p20230611 VALUES [('2023-06-11'), ('2023-06-12')),
PARTITION p20230612 VALUES [('2023-06-12'), ('2023-06-13')),
PARTITION p20230613 VALUES [('2023-06-13'), ('2023-06-14')),
PARTITION p20230614 VALUES [('2023-06-14'), ('2023-06-15')),
PARTITION p20230615 VALUES [('2023-06-15'), ('2023-06-16')),
PARTITION p20230616 VALUES [('2023-06-16'), ('2023-06-17')),
PARTITION p20230617 VALUES [('2023-06-17'), ('2023-06-18')),
PARTITION p20230618 VALUES [('2023-06-18'), ('2023-06-19')),
PARTITION p20230619 VALUES [('2023-06-19'), ('2023-06-20')),
PARTITION p20230620 VALUES [('2023-06-20'), ('2023-06-21')),
PARTITION p20230621 VALUES [('2023-06-21'), ('2023-06-22')),
PARTITION p20230622 VALUES [('2023-06-22'), ('2023-06-23')),
PARTITION p20230623 VALUES [('2023-06-23'), ('2023-06-24')),
PARTITION p20230624 VALUES [('2023-06-24'), ('2023-06-25')),
PARTITION p20230625 VALUES [('2023-06-25'), ('2023-06-26')),
PARTITION p20230626 VALUES [('2023-06-26'), ('2023-06-27')),
PARTITION p20230627 VALUES [('2023-06-27'), ('2023-06-28')),
PARTITION p20230628 VALUES [('2023-06-28'), ('2023-06-29')),
PARTITION p20230629 VALUES [('2023-06-29'), ('2023-06-30')),
PARTITION p20230630 VALUES [('2023-06-30'), ('2023-07-01')),
PARTITION p20230701 VALUES [('2023-07-01'), ('2023-07-02')),
PARTITION p20230702 VALUES [('2023-07-02'), ('2023-07-03')),
PARTITION p20230703 VALUES [('2023-07-03'), ('2023-07-04')),
PARTITION p20230704 VALUES [('2023-07-04'), ('2023-07-05')),
PARTITION p20230705 VALUES [('2023-07-05'), ('2023-07-06')),
PARTITION p20230706 VALUES [('2023-07-06'), ('2023-07-07')),
PARTITION p20230707 VALUES [('2023-07-07'), ('2023-07-08')),
PARTITION p20230708 VALUES [('2023-07-08'), ('2023-07-09')),
PARTITION p20230709 VALUES [('2023-07-09'), ('2023-07-10')),
PARTITION p20230710 VALUES [('2023-07-10'), ('2023-07-11')),
PARTITION p20230711 VALUES [('2023-07-11'), ('2023-07-12')),
PARTITION p20230712 VALUES [('2023-07-12'), ('2023-07-13')),
PARTITION p20230713 VALUES [('2023-07-13'), ('2023-07-14')),
PARTITION p20230714 VALUES [('2023-07-14'), ('2023-07-15')),
PARTITION p20230715 VALUES [('2023-07-15'), ('2023-07-16')),
PARTITION p20230716 VALUES [('2023-07-16'), ('2023-07-17')),
PARTITION p20230717 VALUES [('2023-07-17'), ('2023-07-18')),
PARTITION p20230718 VALUES [('2023-07-18'), ('2023-07-19')),
PARTITION p20230719 VALUES [('2023-07-19'), ('2023-07-20')),
PARTITION p20230720 VALUES [('2023-07-20'), ('2023-07-21')),
PARTITION p20230721 VALUES [('2023-07-21'), ('2023-07-22')))
DISTRIBUTED BY HASH(`date_time`, `pick_network_code`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2"
);